package assignment.day3;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

// @WebServlet belongs to Glassfish Server
@WebServlet(description = "Day 3 Assignment 1", urlPatterns = { "/D3A1CatalogServlet" })
public class D3A1CatalogServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/*
	 * ServletConfig config; ServletContext context;
	 * 
	 * @Override public void init(ServletConfig config) throws ServletException
	 * { this.config = config; this.context = config.getServletContext(); }
	 */
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}

	@Override
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.write("<html><head><title>Categories</title></head><body bgcolor='lavender'>");
		try {
			/*
			 * Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con
			 * = DriverManager.getConnection(
			 * "jdbc:oracle:thin:@10.23.0.100:1521:esgdb10", "scott", "tiger");
			 */

			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("jdbc/mydata");
			Connection con = ds.getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from katalog");
			out.write("<table border='2'>");
			while (rs.next()) {
				out.write("<tr> <td> ");
				out.write(rs.getString(1));
				out.write(" </td> <td> ");
				out.write(rs.getString(2));
				out.write(" </td> </tr>");
			}
			out.write("</table>");
			stmt.close();
			con.close();
		} catch (Exception e) {
			out.write(e.toString());
		}
		out.write("</body></html>");
	}
}

// SQL(PostgreSQL Syntax)
// Drop table products;
// Drop table katalog;
// create table katalog (
// id integer Primary Key,
// category varchar(20) not null
// );
// create table products (
// id integer REFERENCES katalog,
// pid varchar(10) primary key,
// name varchar(20) not null,
// price real not null
// );
// insert into katalog values (1,'mobile');
// insert into katalog values (2,'computer');
// insert into katalog values (3,'bike');
// insert into katalog values (4,'car');
// insert into products values (1,'M001','HTC Desire',15000);
// insert into products values (1,'M101','Galaxy Nexus',24000);
// insert into products values (1,'M201','iPhone4S',46000);
// insert into products values (2,'C001','Dell Latitude',32000);
// insert into products values (2,'C101','HP Pavilion',42000);
// insert into products values (2,'C201','MacBook Pro',69000);
// insert into products values (3,'B001','Bajaj Pulsar',69000);
// insert into products values (3,'B101','Apache',78000);
// insert into products values (3,'B201','Thunderird',75000);
// insert into products values (4,'V001','Mustang',556000);
// insert into products values (4,'V201','Lamborgini',856000);
// insert into products values (4,'V301','Lancer',256000);

